# $Id: Filter.pm,v 1.1 2004/02/29 19:18:42 ibo Exp $

package CGI::FOGE2::Filter;
use strict;
use CGI::FOGE2::Form;
use CGI::FOGE2::Tools;

our @ISA = ();

sub new {
    my $proto = shift;
    my $class = ref($proto) || $proto;
    my %args = @_;

    my $self = {
	name => 'filter',
        form => $args{form},
    };

    bless $self, $class;
    return $self;
}

sub setform {
    my $self = shift;
    my ($form) = @_;
    $self->{form} = $form;
}


sub create_temp_table {
    my $self = shift;
    my $form = $self->{form};
    my $db = $form->dbh_r();
    my $answerTable = $form->{dbAnswer};
    my $columns_in_tables = $self->get_tables();
    my @tables = (keys %$columns_in_tables);
    my @columns = ();

    for my $table (keys %{$columns_in_tables}) {
	for my $col (@{$$columns_in_tables{$table}}) {
	    push @columns, $col;
	}
    }
    
    my $select = join ", ", ($answerTable . '.id', @columns);
    my $from = join(", ", ($answerTable, @tables));

    my $where = $answerTable . ".id = " . $tables[0] . ".id";
    for (my $i = 0; $i < $#tables ; $i++) {
	$where .= " AND " . $tables[$i] . ".id = " . $tables[$i+1] . ".id";
    }

    $where .= ' AND ' . $answerTable . ".id = 10";

    print STDERR "FILTER SAYS SELECT IS  ", $select, "\n";
    print STDERR "FILTER SAYS FROM   IS  ", $from, "\n";
    print STDERR "FILTER SAYS WHERE  IS  ", $where, "\n";

    my $request = "CREATE TEMP TABLE $self->{name} AS select " . $select . "\n" .
	"from " . $from . "\n" .
	"where enquiry = \'" . $form->{enquiry} . "\' AND " . $where . ";\n";
    print STDERR $request;

    my $sth = $db->prepare($request)
        || die "Cannot prepare create TEMP TABLE request: " . $db->errstr;
    $sth->execute()
        || die "Cannot execute create TEMP TABLE request: " . $db->errstr . "\n";

    $self->{sth} = $sth;
 
}
 
sub finish {
    my $self = shift;
    my $sth = $self->{sth};
    $sth->finish();
}

# A hash where the keys are the table names and the
# values are arrays of the columns in those tables
sub get_tables {
    my $self = shift;
    my $form = $self->{form};
    my %tables = ();
    
    for my $section (@{$form->{sections}}) {
	my $table = $section->get_table_name();
	print STDERR "UNE TABLE:   $table .\n"; 
	#next unless defined CGI::param($table);
	for my $question (@{$section->{questions}}) {
	    print STDERR "UNE QUESTION:  ${$question->generate_field_names()}[0].\n"; 
	    #next unless defined CGI::param(${$question->generate_field_names()}[0]);
	    push @{$tables{$table}}, ${$question->generate_field_names()}[0];
	}
    }

    return \%tables;
}
1;

# end of FormStats.pm
